import re

ddl = """
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100) NOT NULL,
    phone_number VARCHAR2(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR2(10) NOT NULL,
    salary NUMBER(8, 2),
    commission_pct NUMBER(2, 2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

COMMENT ON COLUMN employees.employee_id IS 'Unique identifier for the employee.';
COMMENT ON COLUMN employees.first_name IS 'First name of the employee.';
COMMENT ON COLUMN employees.last_name IS 'Last name of the employee.';
COMMENT ON COLUMN employees.email IS 'Email address of the employee.';
COMMENT ON COLUMN employees.phone_number IS 'Phone number of the employee.';
COMMENT ON COLUMN employees.hire_date IS 'Date the employee was hired.';
COMMENT ON COLUMN employees.job_id IS 'Job ID the employee holds.';
COMMENT ON COLUMN employees.salary IS 'Salary of the employee.';
COMMENT ON COLUMN employees.commission_pct IS 'Commission percentage of the employee.';
COMMENT ON COLUMN employees.manager_id IS 'Manager ID of the employee.';
"""

# 正则表达式匹配模式
pattern = r"COMMENT\s+ON\s+COLUMN\s+(?P<table>\w+)\.(?P<column>\w+)\s+IS\s+'(?P<comment>.*)';"

# 使用findall来查找所有匹配项
comments = re.findall(pattern, ddl)

# 创建一个字典来存储结果
column_comments = {}

for match in comments:
    table, column, comment = match
    if table not in column_comments:
        column_comments[table] = {}
    column_comments[table][column] = comment

# 打印结果
# for table, columns in column_comments.items():

#     print(f"Table: {table}")
#     for column, comment in columns.items():
#         print(f"\t{column}: {comment}")
if not column_comments.get('employees').get('department_id'):
    print("dddd")
print(column_comments.get('employees').get('department_id'))